﻿using System;
using System.Diagnostics;
using System.Collections;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Excel;
namespace Maticsoft.Common
{
    /// <summary>
    /// 操作EXCEL导出数据报表的类
    /// Copyright (C) Maticsoft
    /// </summary>
    public class ModelToExcel
    {
        public ModelToExcel()
        {
        }

        #region 操作EXCEL的一个类(需要Excel.dll支持)

        private int titleColorindex = 15;
        /// <summary>
        /// 标题背景色
        /// </summary>
        public int TitleColorIndex
        {
            set { titleColorindex = value; }
            get { return titleColorindex; }
        }

        private DateTime beforeTime;			//Excel启动之前时间
        private DateTime afterTime;				//Excel启动之后时间

        #region 将DataTable的数据导出显示为报表
        /// <summary>
        /// 将DataTable的数据导出显示为报表
        /// </summary>
        /// <param name="strRow">行</param>
        /// <param name="strCol">列</param>
        /// <param name="strValues">值</param>
        /// <param name="FilePath">文件地址</param>
        public void OutputExcel(string[] strRow, string[] strCol, string[] strValues, string FilePath)
        {
            try
            {
                beforeTime = DateTime.Now;

                Excel.Application excel;
                Excel._Workbook xBk;
                Excel._Worksheet xSt;

                excel = new Excel.ApplicationClass();
                xBk = excel.Workbooks.Open(FilePath);//读取文件
                xSt = (Excel._Worksheet)xBk.Sheets[1];//第一个Sheet，sheet的索引从1开始，不是0
                xSt.Name = DateTime.Now.ToShortDateString().ToString();//更改名字

                for (int i = 1; i < strRow.Length; i++)//数组的长度说明需要的表的数量,复制表
                {
                    //xBk.Sheets.Add(After: xSt);
                    xSt.Copy(After: (Excel._Worksheet)xBk.Sheets[i]);//复制到后面
                    ((Excel._Worksheet)xBk.Sheets[i + 1]).Name = DateTime.Now.ToShortDateString().ToString() + "(" + i + ")";//改名字日期加编号
                }

                foreach (Excel._Worksheet ew in xBk.Sheets)//遍历sheet
                {
                    for (int x = 0; x < strRow.Length; x++)
                    {
                        string[] rowIndex = strRow[x].Split(',');//行的值
                        string[] colIndex = strCol[x].Split(',');//列的值
                        string[] values = strValues[x].Split(',');//单元格的值
                        for (int y = 0; y < rowIndex.Length; y++)//赋值
                        {
                            ew.Cells[Convert.ToInt32(rowIndex[y]), Convert.ToInt32(colIndex[y])] = "'" + values[y];
                        }
                    }

                }

                xBk.Save();//保存
                excel.Quit();//退出
                KillExcelProcess();//关闭进程

                //回收
                if (xSt != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                    xSt = null;
                }
                if (xBk != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                    xBk = null;
                }
                if (excel != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    excel = null;
                }
                GC.Collect();//垃圾回收
            }
            finally
            {
                KillExcelProcess();
            }
        #endregion

        }
        #endregion

        #region Kill Excel进程

        /// <summary>
        /// 结束Excel进程
        /// </summary>
        public void KillExcelProcess()
        {
            Process[] myProcesses;
            DateTime startTime;
            myProcesses = Process.GetProcessesByName("Excel");

            //得不到Excel进程ID，暂时只能判断进程启动时间
            foreach (Process myProcess in myProcesses)
            {
                startTime = myProcess.StartTime;

                myProcess.Kill();
            }
        }
        #endregion

        #region  清理过时的Excel文件

        private void ClearFile(string FilePath)
        {
            String[] Files = System.IO.Directory.GetFiles(FilePath);
            if (Files.Length > 10)
            {
                for (int i = 0; i < 10; i++)
                {
                    try
                    {
                        System.IO.File.Delete(Files[i]);
                    }
                    catch
                    {
                    }

                }
            }
        }
        #endregion

    }
}
